@ECHO OFF
REM *******************************************************************************************/
REM	Setup.BAT
REM
REM	Params: 	Server
REM			MDF Path   (odd file filegroup  & primary filegroup)		 
REM			Log Filename	
REM
REM	Dependencies:
REM	The SQL Server utility osql must be located on the computer's path
REM The SQL Server utility sqlcmd must be located on the computer's path
REM
REM
REM       Date                   Author                  Description
REM ----------		------------------		----------------------------------------------------
REM 05/28/2009          Vineet Batta                    Initial script to create database for ISRMSystem.
REM 06/03/2009          Anil Chintala                   Updated the script to create database for CISF Portal
REM 08/13/2009          Wei Qiu                         Add new parameters: username, password.
REM *******************************************************************************************/

REM -- Setting Variables
SET ProjectName=Portal Database Creation
SET DBFileSize=200
SET DBFileGrowth=100
SET LogFileSize=50
SET LogFileGrowth=50
SET ExitCode=0


REM -- Checking Parameters
REM SET CMDFile=%~0
SET CMDFile=Setup
IF "%~1" == "" (GOTO BadParam) ELSE (SET Server=%~1)
IF "%~2" == "" (GOTO BadParam) ELSE (SET MDFPath=%~2)
IF "%~3" == "" (GOTO BadParam) ELSE (SET LDFPath=%~3)
IF "%~4" == "" (GOTO BadParam) ELSE (SET UserName=%~4)
IF "%~5" == "" (GOTO BadParam) ELSE (SET UserPWD=%~5)

SET Database=CISFPortal_CodePlex


SET LogFile="%CMDFile%.log"


REM -- Displaying message on screen
ECHO *******************************************************************************
ECHO Executing %ProjectName% scripts on \\%Server%\%Database%
ECHO at %DATE% %TIME%.
ECHO *******************************************************************************
ECHO Connecting to \\%Server%...

REM -- Writing message to Log File
ECHO *******************************************************************************	>>  %LogFile%
ECHO Executing %ProjectName% on \\%Server%\%Database%		>> %LogFile%
ECHO at %DATE% %TIME%.					>> %LogFile%
ECHO *******************************************************************************						>> %LogFile%
ECHO Connecting to \\%Server%...																			>> %LogFile%

REM -- Connecting to SQL Server
CALL osql -S "%Server%" -d "master" -E -n -b -Q "PRINT 'Success'"	>> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating Database %Database% if it doesn't exist.
ECHO.																										>> %LogFile%
ECHO ------------------------------------------------------------------	 >> %LogFile%
ECHO Creating Database %Database% if it doesn't exist														>> %LogFile%
ECHO ------------------------------------------------------------------	 >> %LogFile%
REM -- Creating Portal Database if doesn't exist...

SET MDF_Filename=%MDFPath%%Database%_Data.mdf
SET LDF_Filename=%LDFPath%%Database%_Log.ldf
SET SQL_Line1=IF NOT EXISTS(SELECT * FROM master.dbo.SysDatabases WHERE Name = '%Database%')
SET SQL_Line2=CREATE DATABASE [%Database%] ON PRIMARY
SET SQL_Line3=(NAME = '%Database%_Data', FILENAME = '%MDF_Filename%', SIZE = %DBFileSize%,  FILEGROWTH = %DBFileGrowth%)
SET SQL_Line4=LOG ON
SET SQL_Line5=(NAME = '%Database%_Log',  FILENAME = '%LDF_Filename%', SIZE = %LogFileSize%, FILEGROWTH = %LogFileGrowth%)

CALL osql -S "%Server%" -d "master" -E -n -b -Q "%SQL_Line1% %SQL_Line2% %SQL_Line3% %SQL_Line4% %SQL_Line5%"	>> %LogFile%

ECHO Database creation is completed for %Database% . >> %LogFile%
ECHO Database creation is completed for %Database% . 

IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO -------------------------------------------------------------------	>> %LogFile%
ECHO Creating Tables and Relationships - CISFPortal					>> %LogFile%
ECHO ------------------------------------------------------------------- 	>> %LogFile%

CALL osql -S "%Server%" -d "%Database%" -E -n -b -i ".\Schema\Schema.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Tables and Relationships - CISFPortal Created successfully. 			>> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%

ECHO ---------------------------------------------------------------------	
ECHO Tables and Relationships - CISFPortal Created successfully. 
ECHO ---------------------------------------------------------------------	


CALL osql -S "%Server%" -d "%Database%" -E -n -b -i ".\Objects\Objects.sql" >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	
ECHO Stored Procedures/Functions - CISFPortal Created successfully. 			
ECHO ---------------------------------------------------------------------	

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Stored Procedures/Functions - CISFPortal Created successfully. 			>> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%


CALL SQLCMD.EXE -S "%Server%" -d "%Database%" -E -b -v UserName="%UserName%" UserPWD="%UserPWD%" -i "Permission.sql" >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	
ECHO Setting permissions - CISFPortal 
ECHO ---------------------------------------------------------------------	

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Setting permission - CISFPortal							>> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%


CALL osql -S "%Server%" -d "%Database%" -E -n -b -i ".\Data\DefaultData.sql" >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	
ECHO Inserted Role Data - CISFPortal 
ECHO ---------------------------------------------------------------------	

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Inserted Role Data - CISFPortal							>> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%


REM -- Displaying message on screen
ECHO *******************************************************************************
ECHO All the %ProjectName% - Scripts executed successfully on \\%Server%\%Database%
ECHO at %DATE% %TIME%.
ECHO.
ECHO Please check the log file %LogFile% for details.
ECHO *******************************************************************************

REM -- Writing message to Log File
ECHO.																										>> %LogFile%
ECHO *******************************************************************************						>> %LogFile%
ECHO All the %ProjectName% scripts executed successfully on \\%Server%\%Database%						>> %LogFile%
ECHO at %DATE% %TIME%.																						>> %LogFile%
ECHO *******************************************************************************						>> %LogFile%

GOTO End


REM -- Subroutine: BadParam --------------------------------------------------------
:BadParam
ECHO *******************************************************************************
ECHO Please enter valid parameters to execute this script.
ECHO.
ECHO %CMDFile% "Server"  "MDF Path " "Log Path" "UserName" "UserPassword" 
ECHO.
ECHO For example: %CMDFile% "SERVER" "H:\DataFiles\"  "H:\Logfile\" "user" "password"
ECHO.
ECHO NOTE: Make sure your MDF and LDF paths end with a back slash (\).
ECHO *******************************************************************************

SET ExitCode=1
GOTO End
REM --------------------------------------------------------------------------------


REM -- Subroutine: Crash -----------------------------------------------------------
:Crash
ECHO *******************************************************************************
ECHO Failed to execute all the  %ProjectName% scripts on \\%Server%\%Database%
ECHO at %DATE% %TIME%.
ECHO.
ECHO Please check the log file %LogFile% for Errors!
ECHO *******************************************************************************

SET ExitCode=1
GOTO End
REM --------------------------------------------------------------------------------


REM -- Subroutine: End -------------------------------------------------------------
:End

REM --------------------------------------------------------------------------------
